Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Writing queries for performance

This section provides a collection of tips and guidelines to follow when writing queries. For example, a query that processes a large number of rows performs best if it uses NO–LOCK, lookahead cursors, a large cache size, and a small field list.

These tips and guidelines might improve the performance of your DataServer applications. To assess the usefulness of a particular suggestion, apply it, then use the DEBUG diagnostic options to gather statistics on how your application runs:

Use FOR EACH, GET, and OPEN QUERY statements rather than FIND statements, which generally perform more slowly. Consider using the FOR FIRST statement instead of FIND FIRST.

The only exception is that FIND LAST is faster than GET LAST. This is because GET LAST causes the client to process all of the records; the FIND LAST statement allows the server to retrieve the last record.

Use field lists.
Use the QUERY–TUNING options.
Use lookahead cursors.
Use NO–LOCK where possible.
Avoid specifying lock upgrades. Instead, allow the DataServer and the MSS data source to handle lock upgrades.
Do not ask for a particular ordering of results with USE–INDEX or BY clauses unless your application requires it. Instead, allow the DataServer and the MSS data source to determine the most efficient index (if any) for processing a query and avoid the overhead of sorting results.
If you use a BY clause that will sort a large amount of data, make sure a corresponding index exists in your data source to make sorting efficient. In some cases it may also be desirable to have indexes over columns used in WHERE clause selection criteria.
For aggregates, use either the RUN–STORED–PROCEDURE send–sql–statement syntax or a Progress SQL statement. If you use a Progress SQL statement with a cursor, declare the cursor read-only.
When you test for the existence of a record, use the CAN–FIND FIRST function, which does not retrieve the record if the DataServer passes the entire WHERE clause to the MSS data source for processing. However, avoid nesting CAN–FIND functions.
Avoid using the RECID function. Instead, use the ROWID function.

See the "Analyzing performance" section for information on collecting statistics.